﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace OneMake.Model
{
    //客服总绩效
    public class report_kf_total_scope
    {
        [MapToField(FieldName = "日期")]
        public DateTime stat_datetime { get; set; }

        [MapToField(FieldName = "接待人数")]
        public int n_serve { get; set; }

        [MapToField(FieldName = "询单人数")]
        public int n_inquire { get; set; }

        [MapToField(FieldName = "付款人数")]
        public int n_paid { get; set; }

        [MapToField(FieldName = "付款金额")]
        public double n_paid_amount { get; set; }

        [MapToField(FieldName = "询单人均产值")]
        public double avg_paid_amount { get; set; }

        [MapToField(FieldName = "平均响应时间（秒）")]
        public double avg_wwanswer { get; set; }

        [MapToField(FieldName = "旺旺回复率")]
        public double per_wwreply { get; set; }

        [MapToField(FieldName = "客单价（元）")]
        public double paid_uprice { get; set; }

        [MapToField(FieldName = "客件数（件）")]
        public double n_paid_prds { get; set; }

        [MapToField(FieldName = "下单成功率（%）")]
        public double per_todaypaid { get; set; }

        [MapToField(FieldName = "付款成功率（%）")]
        public double per_finalpaid { get; set; }

        [MapToField(FieldName = "完成退款件数（件）")]
        public double n_done_amount { get; set; }

        [MapToField(FieldName = "完成退款金额（元）")]
        public double n_done_money { get; set; }
    }

    partial class ReportHelper
    {
        private T _get_sum_kf_scope_total<T>(PetaPoco.Database db, string tablename, string fieldName, int shopId, DateTime day)
        {
            string sql = string.Format(@"select sum({0}) from {1} where shopid=@0 and stat_datetime=@1", fieldName, tablename);
            return db.SafeExecuteScalar<T>(sql, shopId, day.ToShortDateString());
        }
        private T _get_avg_kf_scope_total<T>(PetaPoco.Database db, string tablename, string fieldName, int shopId, DateTime day)
        {
            string sql = string.Format(@"select round(avg({0}),2) from {1} where shopid=@0 and stat_datetime=@1 AND {0}<>0", fieldName, tablename, fieldName);
            return db.SafeExecuteScalar<T>(sql, shopId, day.ToShortDateString());
        }
        public report_kf_total_scope get_report_kf_total_scope(int shopid, DateTime day)
        {
            var db = new PetaPoco.Database("mysql" + UserSession.Network);
            report_kf_total_scope report = new report_kf_total_scope();

            report.stat_datetime = day.Date;
            report.n_serve = _get_sum_kf_scope_total<int>(db, "ct_d_jxdb", "n_serve", shopid, day);
            report.n_inquire = _get_sum_kf_scope_total<int>(db, "ct_d_jxdb", "n_inquire", shopid, day);
            report.n_paid = _get_sum_kf_scope_total<int>(db, "ct_d_jxdb", "n_paid", shopid, day);
            report.n_paid_amount = _get_sum_kf_scope_total<double>(db, "ct_d_jxdb", "n_paid_amount", shopid, day);

            if (report.n_inquire == 0)
                return null;

            report.avg_paid_amount = Math.Round(report.n_paid_amount / report.n_inquire, 2);

            report.avg_wwanswer = _get_avg_kf_scope_total<double>(db, "ct_d_gzldb", "avg_wwanswer", shopid, day);
            report.per_wwreply = _get_avg_kf_scope_total<double>(db, "ct_d_gzldb", "per_wwreply", shopid, day);
            report.paid_uprice = _get_avg_kf_scope_total<double>(db, "ct_d_kdjdb", "paid_uprice", shopid, day);
            report.n_paid_prds = _get_avg_kf_scope_total<double>(db, "ct_d_kjsdb", "n_paid_prds", shopid, day);
            report.per_todaypaid = _get_avg_kf_scope_total<double>(db, "ct_d_cgldb", "per_todaypaid", shopid, day);
            report.per_finalpaid = _get_avg_kf_scope_total<double>(db, "ct_d_cgldb", "per_finalpaid", shopid, day);

            report.n_done_amount = _get_sum_kf_scope_total<double>(db, "ct_d_tkqkdb", "n_done_amount", shopid, day);
            report.n_done_money = _get_sum_kf_scope_total<double>(db, "ct_d_tkqkdb", "n_done_money", shopid, day);

            return report;
        }

        public List<report_kf_total_scope> get_report_kf_total_scopes(int shopid, DateTime beginTime, DateTime endTime)
        {
            System.Diagnostics.Debug.Assert(beginTime <= endTime);

            List<report_kf_total_scope> reports = new List<report_kf_total_scope>();
            DateTime tempTime = beginTime;
            do
            {
                report_kf_total_scope report = get_report_kf_total_scope(shopid, tempTime);
                if (report != null)
                    reports.Add(report);
                tempTime = tempTime.AddDays(1);
            }
            while (tempTime <= endTime);

            return reports;
        }
    }
}
